import { createConnection } from "./dbutils.js";

const DEMAND_2_COUNT_DEVICE_ON="SELECT COUNT(DISTINCT b.device_id) AS total  \
FROM usmschis.t_his_telesignaldata20230101 b \
JOIN (  \
    SELECT a.device_id, a.mete_id, MAX(a.record_time) AS max_time \
    FROM usmschis.t_his_telesignaldata20230101 a \
    WHERE a.mete_id IN ('0187001001', '0187002001') \
    GROUP BY a.device_id, a.mete_id \
) t ON b.device_id = t.device_id  \
    AND b.mete_id = t.mete_id  \
    AND b.record_time = t.max_time \
WHERE not((b.mete_id = '0187002001' \
AND b.mete_value = 1) \
or (b.mete_id = '0187001001' and b.mete_value = 0));";

function queryDeviceCountON(success) {
    let connection = createConnection();
    connection.connect();
    connection.query(DEMAND_2_COUNT_DEVICE_ON, (err, res) => {
        if (err == null) {
            console.log(res);
            success(res);
        } else {
            throw new Error(err);
        }
        console.log(res);
    });
    connection.end();
}


const DEMAND_2_COUNT_DEVICE_USED = "SELECT COUNT(DISTINCT b.device_id) AS total \
FROM usmschis.t_his_telesignaldata20230101 b  \
JOIN (  \
    SELECT a.device_id, a.mete_id, MAX(a.record_time) AS max_time  \
    FROM usmschis.t_his_telesignaldata20230101 a  \
    WHERE a.mete_id IN ('0187001001', '0187002001') GROUP BY a.device_id, a.mete_id) t \
    ON b.device_id = t.device_id   \
    AND b.mete_id = t.mete_id   \
    AND b.record_time = t.max_time  \
WHERE b.mete_id = '0187002001' AND b.mete_value = 1;";

function queryDeviceCountUsed(success) {
    let connection = createConnection();
    connection.connect();
    connection.query(DEMAND_2_COUNT_DEVICE_USED, (err, res) => {
        if (err == null) {
            console.log(res);
            success(res);
        } else {
            throw new Error(err);
        }
        console.log(res);
    });
    connection.end();
}

const DEMAND_2_COUNT_DEVICE_OFF = "SELECT COUNT(DISTINCT b.device_id)  AS total \
FROM usmschis.t_his_telesignaldata20230101 b  \
JOIN (  \
    SELECT a.device_id, a.mete_id, MAX(a.record_time) AS max_time  \
    FROM usmschis.t_his_telesignaldata20230101 a  \
    WHERE a.mete_id IN ('0187001001', '0187002001')  \
    GROUP BY a.device_id, a.mete_id  ) t \
    ON b.device_id = t.device_id   \
    AND b.mete_id = t.mete_id   \
    AND b.record_time = t.max_time  \
    WHERE b.mete_id = '0187001001' AND b.mete_value = 0;"

function queryDeviceCountOFF(success) {
    let connection = createConnection();
    connection.connect();
    connection.query(DEMAND_2_COUNT_DEVICE_OFF, (err, res) => {
        if (err == null) {
            console.log(res);
            success(res);
        } else {
            throw new Error(err);
        }
    });
    connection.end();
}

const DEMAND_2_POWER_LIST = 'SELECT \
b.name, \
b.time, \
b.power \
FROM    \
cloud_computing_demo.power b \
WHERE \
b.NAME IN ( \
SELECT \
    t.NAME \
FROM \
    ( \
    SELECT \
        a.name,\
        a.time, \
        max( a.power ) \
    FROM \
        cloud_computing_demo.power a \
    WHERE \
        a.time = ( SELECT max( time ) FROM cloud_computing_demo.power  ) \
    GROUP BY \
        a.name, \
        a.time \
    ORDER BY \
        max( a.power ) DESC \
    LIMIT 5 \
) t) and  b.time > "2023-01-16"'

function queryPowerList(success) {
    let connection = createConnection();
    connection.connect();
    connection.query(DEMAND_2_POWER_LIST, (err, res) => {
        if (err == null) {
            console.log(res);
            success(res);
        } else {
            throw new Error(err);
        }
    });
    connection.end();
}



const QUERY_TEMPLATE = 'SELECT \
	b.device_name, \
	b.mete_name, \
	b.mete_value, \
	b.record_time \
FROM \
	cloud_computing_demo.temperature b \
	JOIN ( \
	SELECT \
		device_name \
	FROM \
		cloud_computing_demo.temperature  \
	WHERE \
		record_time = ( SELECT MAX( record_time ) FROM cloud_computing_demo.temperature ) \
		AND device_name NOT LIKE "%热%" \
	ORDER BY \
		mete_value DESC \
	LIMIT 5 \
	) a ON b.device_name = a.device_name \
	WHERE b.record_time >= "2023-1-1 19:34:48" \
    ORDER BY b.record_time ASC '


function queryTemplateList(success) {
    let connection = createConnection();
    connection.connect();
    connection.query(QUERY_TEMPLATE, ['温度'], (err, res) => {
        if (err == null) {
            console.log(res);
            success(res);
        } else {
            throw new Error(err);
        }
    });
    connection.end();
}


const QUERY_HUMIDNESS = "SELECT \
b.device_name, \
b.mete_name, \
b.mete_value, \
b.record_time  \
FROM \
cloud_computing_demo.humidity b \
JOIN ( \
SELECT \
    device_name \
FROM \
    cloud_computing_demo.humidity  \
WHERE \
    record_time > DATE_ADD(( SELECT MAX( record_time ) FROM cloud_computing_demo.humidity WHERE device_name NOT LIKE '%热%' ), INTERVAL - 5 MINUTE ) \
ORDER BY \
    mete_value DESC \
    LIMIT 5  \
) a ON b.device_name = a.device_name \
WHERE \
b.record_time >= '2023-01-01 22:59:38' \
ORDER BY \
b.record_time, \
b.device_name"


function queryHumidnessList(success) {
    let connection = createConnection();
    connection.connect();
    connection.query(QUERY_HUMIDNESS, (err, res) => {
        if (err == null) {
            console.log(res);
            success(res);
        } else {
            throw new Error(err);
        }
    });
    connection.end();
}


const QUERY_ELECTTRICITY = "SELECT \
	b.设备名称 as device_name, \
	b.用电量 as mete_value, \
	b.日期 as record_time  \
FROM \
	cloud_computing_demo.power_data b \
	JOIN ( SELECT 设备名称 FROM cloud_computing_demo.power_data a ORDER BY 日期 DESC,用电量 DESC LIMIT 5 ) t ON t.设备名称 = b.设备名称  \
ORDER BY \
	b.日期 ASC"


function queryElectricityList(success) {
    let connection = createConnection();
    connection.connect();
    connection.query(QUERY_ELECTTRICITY, (err, res) => {
        if (err == null) {
            console.log(res);
            success(res);
        } else {
            throw new Error(err);
        }
    });
    connection.end();
}



export { queryDeviceCountUsed, queryDeviceCountOFF, queryPowerList, queryTemplateList, queryHumidnessList, queryDeviceCountON, queryElectricityList };
